﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using MySql.Data.MySqlClient;

namespace ATM
{
    class adminDB
    {
        private string connection = "datasource=localhost;port=3306;username=root;password=1234";
        private int exist;

        public bool checkAdmin(int p)
        {
            try
            {
                MySqlConnection myconn = new MySqlConnection(connection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.admin where password='" + p + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();

                while (myreader.Read())
                {
                    exist = 1;
                }

                myconn.Close();

                if (exist == 1)
                    return true;
                else
                    return false;
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        /** end of checkAdmin **/
        /** transactionsAll **/
        public DataSet transactionsAll()
        {
            DataSet ds = new DataSet();
            try
            {
                MySqlConnection myconn = new MySqlConnection(connection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.history;", myconn);
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
                dataAdapter.SelectCommand = SelectCommand;
                dataAdapter.Fill(ds);
                return ds;
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                return ds;

            }
        }
        /** end of transactionsAll **/
        /** show one user transactions **/
        public DataSet transactionsUser(int id)
        {
            DataSet ds = new DataSet();
            int an = 0;
            try
            {
                MySqlConnection myconn = new MySqlConnection(connection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where ID='" + id + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();

                while (myreader.Read())
                {
                    an = int.Parse((myreader["Account_Number"].ToString()));
                }

                myconn.Close();

                MySqlConnection myconn2 = new MySqlConnection(connection);
                MySqlCommand SelectCommand2 = new MySqlCommand("SELECT * FROM atm.history WHERE Account_Number ='" + an + "';", myconn);
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
                dataAdapter.SelectCommand = SelectCommand2;
                dataAdapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return ds;

            }
        }
        /** end of show one user transactions **/
        /** add user **/
        public bool addUser(string name, string email, int accountNumber, float balance)
        {
            try
            {
                MySqlConnection myconnection = new MySqlConnection(connection);

                MySqlCommand InsertCommand = new MySqlCommand("INSERT INTO atm.atm_customers VALUES (0, '" + name + "' ,'" + email + "', '" + accountNumber + "', '" + balance + "' );", myconnection);
                MySqlDataReader myreader;

                myconnection.Open();
                myreader = InsertCommand.ExecuteReader();
                myconnection.Close();
                return true;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); return false; }
        }
        /** end of add user **/
        //** user exist  **//
        public bool userExist(int accountNumber)
        { 
            int an = 0;
            try
            {
                MySqlConnection myconn = new MySqlConnection(connection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers WHERE Account_Number='" + accountNumber + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();

                while (myreader.Read())
                {
                    an = int.Parse((myreader["Account_Number"].ToString()));
                }

                myconn.Close();
                if (an == 0)
                    return true; 
                else
                    return false;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); return false; }
        }
        //** end of user exist **//
        /** email exist **/
        public bool emailExist(string email)
        {
            int an = 0;
            try
            {
                MySqlConnection myconn = new MySqlConnection(connection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers WHERE EMail='" + email + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();

                while (myreader.Read())
                {
                    an = int.Parse((myreader["Account_Number"].ToString()));
                }

                myconn.Close();
                if (an == 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); return false; }

        }
        /** email exist **/
        /** delete user **/
        public bool deleteUser(int id)
        {
            int an = 0;
            try
            {
                MySqlConnection myconn = new MySqlConnection(connection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers WHERE ID='" + id + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();

                while (myreader.Read())
                {
                    an = int.Parse((myreader["Account_Number"].ToString()));
                }

                myconn.Close();
              
                // delete all the transactions from history
                MySqlConnection myconnection2 = new MySqlConnection(connection);
                MySqlCommand deleteCommand2 = new MySqlCommand("DELETE FROM atm.history WHERE Account_Number ='" + an + "';", myconnection2);
                MySqlDataReader myreader3;

                myconnection2.Open();
                myreader3 = deleteCommand2.ExecuteReader();
                myconnection2.Close();

                // delete the account data from atm_customers
                MySqlConnection myconnection = new MySqlConnection(connection);
                MySqlCommand deleteCommand = new MySqlCommand("DELETE FROM atm.atm_customers WHERE ID ='" + id + "';", myconnection);
                MySqlDataReader myreader2;

                myconnection.Open();
                myreader2 = deleteCommand.ExecuteReader();
                myconnection.Close();
                return true;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); return false; }
        }
        /** end of delete user **/
         

    }
}
